package utils;

import com.mysql.cj.jdbc.CallableStatement;
import dbc.DBConnection;

import java.sql.*;

public class DataUtil extends DBConnection implements IDataUtil{
    private  Connection conn  = null;
    private PreparedStatement ptmt = null;
    private ResultSet rs = null;
    private int rows = 0;
    private CallableStatement ctmt = null;

    @Override
    public void close(Connection conn, Statement stmt, ResultSet rs) {
        this.closeConnection( conn,  stmt,  rs);
    }

    @Override
    public void close(Connection conn, Statement stmt) {
        this.closeConnection( conn,  stmt);
    }

    @Override
    public int insert (String sql, Object[]para){//添加数据
        try {
            conn = this.getConnection();
            ptmt = conn.prepareStatement(sql);
            if(para!=null) {
                for (int i = 1; i < para.length + 1; i++) {
                    ptmt.setObject(i, para[i - 1]);
                }
            }
            rows = ptmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, ptmt);
        }
        return rows;
    }


    @Override
    public int update(String sql, Object[] para) {//修改数据
        try {
            conn = this.getConnection();
            ptmt = conn.prepareStatement(sql);
            if(para!=null) {
                for (int i = 1; i < para.length + 1; i++) {
                    ptmt.setObject(i, para[i - 1]);
                }
            }
            rows = ptmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close(conn,ptmt);
        }
        return rows;
    }

    @Override
    public int delete(String sql, Object[] para) {//删除数据
        try {
            conn = this.getConnection();
            ptmt = conn.prepareStatement(sql);
            if(para!=null) {
                for (int i = 1; i < para.length + 1; i++) {
                    ptmt.setObject(i, para[i - 1]);
                }
            }
            rows = ptmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close(conn,ptmt);
        }
        return rows;
    }

    @Override
    public ResultSet select(String sql, Object[] para) {//查看数据
        try {
            conn = this.getConnection();
            ptmt = conn.prepareStatement(
                    sql,
                    ResultSet.TYPE_SCROLL_INSENSITIVE  //实现双向滚动,就是rs.next()只能从上到下滚动
                    , ResultSet.CONCUR_READ_ONLY); //只读标识
            if(para!=null) {
                for (int i = 1; i < para.length + 1; i++) {
                    ptmt.setObject(i, para[i - 1]);
                }
            }
            rs = ptmt.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rs;
    }

    @Override
    public ResultSet callProcedureWithQuery(String proName, Object[] para) {//调用存储过程
        try {
            conn = this.getConnection();
            ctmt = (CallableStatement) conn.prepareCall(proName);
            rs = ctmt.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * 返回查询表中的字段数量
     */
    @Override
    public int fieldNum(String sql) {
        int columnCount = 0;
        try {
            conn = this.getConnection();
            // 假设你已经创建了一个 Connection 对象名为 connection
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);

            // 获取 ResultSetMetaData
            ResultSetMetaData metaData = resultSet.getMetaData();
            // 获取列数（字段数量）
            columnCount = metaData.getColumnCount();
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
        return columnCount;
    }

    /**
     * 返回查询表中的字段的行数，就是有多少条数据
     *
     * @param rs
     */
    @Override
    public int lineNum(ResultSet rs) throws SQLException {
        int num=0;
        while(rs.next()){
            num++;
        }
        return num;
    }
}

